CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SCOTT"."PKG_MONTH" is

  ------------------------------------------- 计算股票月线级别的基础数据 -------------------------
  PROCEDURE WRITE_STOCK_MONTH as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 这个月的开始日期
    v_begin_date date;
    -- 这个月的结束日期
    v_end_date date;
    -- 这个月的最高价
    v_highest_price number;
    -- 这个月的最低价
    v_lowest_price number;
    -- 这个月的开盘价
    v_open_price number;
    -- 这个月的收盘价
    v_close_price number;
    -- 这个月的成交量
    v_volume number;
    -- 这个月的成交额
    v_turnover number;
    -- 返回STOCK_TRANSACTION_DATA表中的code_字段（不包括重复的）
    cursor cur_all_stock_code is
      select distinct std.code_ from stock_transaction_data std;
    -- 查找开始日期、结束日期、最高价、最低价、成交量、成交额
    cursor cur_some_basic_data is
      select min(t.date_) begin_date,
             max(t.date_) end_date,
             max(t.highest_price) highest_price,
             min(t.lowest_price) lowest_price,
             sum(t.volume) volume,
             sum(t.turnover) turnover
        from stock_transaction_data t
       where t.code_ = v_code
       group by substr(to_char(t.date_, 'yyyy-mm-dd'), 1, 7);
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;

      -- 查找开始日期、结束日期、最高价、最低价、成交量、成交额
      for j in cur_some_basic_data loop
        v_begin_date    := j.begin_date;
        v_end_date      := j.end_date;
        v_highest_price := j.highest_price;
        v_lowest_price  := j.lowest_price;
        v_volume        := j.volume;
        v_turnover      := j.turnover;

        -- 查找开盘价
        select t.open_price
          into v_open_price
          from stock_transaction_data t
         where t.code_ = v_code
           and t.date_ = v_begin_date;
        -- 查找收盘价
        select t.close_price
          into v_close_price
          from stock_transaction_data t
         where t.code_ = v_code
           and t.date_ = v_end_date;

        -- 插入数据
        insert into stock_month
          (begin_date,
           end_date,
           code_,
           open_price,
           close_price,
           highest_price,
           lowest_price,
           volume,
           turnover)
        values
          (v_begin_date,
           v_end_date,
           v_code,
           v_open_price,
           v_close_price,
           v_highest_price,
           v_lowest_price,
           v_volume,
           v_turnover);
      end loop;
    end loop;
    commit;
  end WRITE_STOCK_MONTH;

  ----------------------------- 计算某一个月的所有股票的基础数据 -------------------------------------
  procedure WRITE_STOCK_MONTH_BY_DATE(p_begin_date varchar2,
                                      p_end_date   varchar2) as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 分别代表某只股票在某个星期的最高价，最低价，开盘价，收盘价，成交量、成交额、记录数、开始日期和结束日期
    v_highest_price number;
    v_lowest_price  number;
    v_open_price    number;
    v_close_price   number;
    v_sum_volume    number;
    v_sum_turnover  number;
    v_num           number;
    v_begin_date    date;
    v_end_date      date;
    -- define cursor section.返回全部CODE_
    cursor cur_all_stock_code is
      select distinct t.code_
        from stock_transaction_data t
       order by t.code_ asc;
  begin
    for i in cur_all_stock_code loop
      v_code := i.code_;
      select count(*)
        into v_num
        from stock_transaction_data t
       where t.code_ = v_code
         and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
             to_date(p_end_date, 'yyyy-mm-dd');

      if v_num != 0 then
        -- dbms_output.put_line(v_code);
        -- 计算某只股票在某个星期的最高价，最低价，开始日期，结束日期，开盘价，收盘价，成交量和成交额
        select max(t.highest_price),
               min(t.lowest_price),
               min(t.date_),
               max(t.date_)
          into v_highest_price, v_lowest_price, v_begin_date, v_end_date
          from stock_transaction_data t
         where t.code_ = v_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd')
         order by t.date_ asc;
        select *
          into v_open_price
          from (select t.open_price
                  from stock_transaction_data t
                 where t.code_ = v_code
                   and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
                       to_date(p_end_date, 'yyyy-mm-dd')
                 order by t.date_ asc)
         where rownum <= 1;
        select *
          into v_close_price
          from (select t.close_price
                  from stock_transaction_data t
                 where t.code_ = v_code
                   and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
                       to_date(p_end_date, 'yyyy-mm-dd')
                 order by t.date_ desc)
         where rownum <= 1;
        select sum(t.volume)
          into v_sum_volume
          from stock_transaction_data t
         where t.code_ = v_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd');
        select sum(t.turnover)
          into v_sum_turnover
          from stock_transaction_data t
         where t.code_ = v_code
           and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
               to_date(p_end_date, 'yyyy-mm-dd');

        insert into stock_month
          (code_,
           begin_date,
           end_date,
           highest_price,
           lowest_price,
           open_price,
           close_price,
           volume,
           turnover)
        values
          (v_code,
           v_begin_date,
           v_end_date,
           v_highest_price,
           v_lowest_price,
           v_open_price,
           v_close_price,
           v_sum_volume,
           v_sum_turnover);
        commit;
      end if;
    end loop;
  end WRITE_STOCK_MONTH_BY_DATE;

  /*--------------------------- 计算月线级别，所有股票的5日均线 ------------------------*/
  procedure WRITE_MA5 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示5天收盘价的和
      v_five_sum number := 0;
      -- 表示5天收盘价的平均值
      v_five_average number := 0;
      -- 定义一个含有5个数值型数据的数组
      type type_array is varray(5) of number;
      array_five type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_month t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_all_stock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_month t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code     := i.code_;
        array_five := type_array();

        for j in cur_all_stock_close_price loop
          array_five.extend; -- 扩展数组，扩展一个元素
          array_five(array_five.count) := j.close_price;

          if mod(array_five.count, 5) = 0 then
            v_five_sum := 0;

            for x in 1 .. array_five.count loop
              -- 求5天收盘价的和
              v_five_sum := v_five_sum + array_five(x);
            end loop;

            -- 删除数组中的第一个元素，将其与4个元素向前挪一位，并删除下标为5的元素
            for y in 1 .. array_five.count - 1 loop
              array_five(y) := array_five(y + 1);
            end loop;
            array_five.trim;

            -- 5天收盘价的平均值
            v_five_average := v_five_sum / 5;

            -- 向所有记录的MA5列插入5天收盘价的平均值
            update stock_month t
               set t.ma5 = round(v_five_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA5;

  /*--------------------------- 计算月线级别，所有股票的10日均线 ------------------------*/
  procedure WRITE_MA10 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示10天收盘价的和
      v_ten_sum number := 0;
      -- 表示10天收盘价的平均值
      v_ten_average number := 0;
      -- 定义一个含有10个数值型数据的数组
      type type_array is varray(10) of number;
      array_ten type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_month t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_all_stock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_month t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code    := i.code_;
        array_ten := type_array();

        for j in cur_all_stock_close_price loop
          array_ten.extend; -- 扩展数组，扩展一个元素
          array_ten(array_ten.count) := j.close_price;

          if mod(array_ten.count, 10) = 0 then
            v_ten_sum := 0;

            for x in 1 .. array_ten.count loop
              -- 求10天收盘价的和
              v_ten_sum := v_ten_sum + array_ten(x);
            end loop;

            -- 删除数组中的第一个元素，将其与9个元素向前挪一位，并删除下标为10的元素
            for y in 1 .. array_ten.count - 1 loop
              array_ten(y) := array_ten(y + 1);
            end loop;
            array_ten.trim;

            -- 10天收盘价的平均值
            v_ten_average := v_ten_sum / 10;

            -- 向所有记录的MA10列插入10天收盘价的平均值
            update stock_month t
               set t.ma10 = round(v_ten_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA10;

  /*--------------------------- 计算月线级别，所有股票的20日均线 ------------------------*/
  procedure WRITE_MA20 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示20天收盘价的和
      v_twenty_sum number := 0;
      -- 表示20天收盘价的平均值
      v_twenty_average number := 0;
      -- 定义一个含有20个数值型数据的数组
      type type_array is varray(20) of number;
      array_twenty type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_month t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_al_sStock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_month t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code       := i.code_;
        array_twenty := type_array();

        for j in cur_al_sStock_close_price loop
          array_twenty.extend; -- 扩展数组，扩展一个元素
          array_twenty(array_twenty.count) := j.close_price;

          if mod(array_twenty.count, 20) = 0 then
            v_twenty_sum := 0;

            for x in 1 .. array_twenty.count loop
              -- 求20天收盘价的和
              v_twenty_sum := v_twenty_sum + array_twenty(x);
            end loop;

            -- 删除数组中的第一个元素，将其与19个元素向前挪一位，并删除下标为20的元素
            for y in 1 .. array_twenty.count - 1 loop
              array_twenty(y) := array_twenty(y + 1);
            end loop;
            array_twenty.trim;

            -- 20天收盘价的平均值
            v_twenty_average := v_twenty_sum / 20;

            -- 向所有记录的MA20列插入20天收盘价的平均值
            update stock_month t
               set t.ma20 = round(v_twenty_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA20;

  /*--------------------------- 计算月线级别，所有股票的60日均线 ------------------------*/
  procedure WRITE_MA60 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示60天收盘价的和
      v_sixty_sum number := 0;
      -- 表示60天收盘价的平均值
      v_sixty_average number := 0;
      -- 定义一个含有60个数值型数据的数组
      type type_array is varray(60) of number;
      array_sixty type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_month t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_all_stock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_month t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code      := i.code_;
        array_sixty := type_array();

        for j in cur_all_stock_close_price loop
          array_sixty.extend; -- 扩展数组，扩展一个元素
          array_sixty(array_sixty.count) := j.close_price;

          if mod(array_sixty.count, 60) = 0 then
            v_sixty_sum := 0;
            for x in 1 .. array_sixty.count loop
              -- 求60天收盘价的和
              v_sixty_sum := v_sixty_sum + array_sixty(x);
            end loop;

            -- 删除数组中的第一个元素，将其与59个元素向前挪一位，并删除下标为60的元素
            for y in 1 .. array_sixty.count - 1 loop
              array_sixty(y) := array_sixty(y + 1);
            end loop;
            array_sixty.trim;

            -- 60天收盘价的平均值
            v_sixty_average := v_sixty_sum / 60;

            -- 向所有记录的MA60列插入60天收盘价的平均值
            update stock_month t
               set t.ma60 = round(v_sixty_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA60;

  /*--------------------------- 计算月线级别，所有股票的120日均线 ------------------------*/
  procedure WRITE_MA120 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示120天收盘价的和
      v_one_hundred_twenty_sum number := 0;
      -- 表示120天收盘价的平均值
      v_one_hundred_twenty_average number := 0;
      -- 定义一个含有120个数值型数据的数组
      type type_array is varray(120) of number;
      array_one_hundred_twenty type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_month t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_all_stock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_month t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code                   := i.code_;
        array_one_hundred_twenty := type_array();

        for j in cur_all_stock_close_price loop
          array_one_hundred_twenty.extend; -- 扩展数组，扩展一个元素
          array_one_hundred_twenty(array_one_hundred_twenty.count) := j.close_price;

          if mod(array_one_hundred_twenty.count, 120) = 0 then
            v_one_hundred_twenty_sum := 0;

            for x in 1 .. array_one_hundred_twenty.count loop
              -- 求120天收盘价的和
              v_one_hundred_twenty_sum := v_one_hundred_twenty_sum +
                                          array_one_hundred_twenty(x);
            end loop;

            -- 删除数组中的第一个元素，将其与119个元素向前挪一位，并删除下标为120的元素
            for y in 1 .. array_one_hundred_twenty.count - 1 loop
              array_one_hundred_twenty(y) := array_one_hundred_twenty(y + 1);
            end loop;
            array_one_hundred_twenty.trim;

            -- 120天收盘价的平均值
            v_one_hundred_twenty_average := v_one_hundred_twenty_sum / 120;

            -- 向所有记录的MA120列插入120天收盘价的平均值
            update stock_month t
               set t.ma120 = round(v_one_hundred_twenty_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA120;

  /*--------------------------- 计算月线级别，所有股票的250日均线 ------------------------*/
  procedure WRITE_MA250 is
  begin
    declare
      -- 表示code_
      v_code varchar2(10);
      -- 表示250天收盘价的和
      v_one_hundred_fifty_sum number := 0;
      -- 表示250天收盘价的平均值
      v_one_hundred_fifty_average number := 0;
      -- 定义一个含有250个数值型数据的数组
      type type_array is varray(250) of number;
      array_one_hundred_fifty type_array := type_array();
      -- 返回全部code_
      cursor cur_all_stock_code is
        select distinct t.code_ from stock_month t;
      -- 返回某只股票的收盘价、开始时间、结束时间，并按开始时间升序排列
      cursor cur_all_stock_close_price is
        select t.close_price, t.begin_date, t.end_date
          from stock_month t
         where t.code_ = v_code
         order by t.begin_date asc;
    begin
      for i in cur_all_stock_code loop
        v_code                  := i.code_;
        array_one_hundred_fifty := type_array();

        for j in cur_all_stock_close_price loop
          array_one_hundred_fifty.extend; -- 扩展数组，扩展一个元素
          array_one_hundred_fifty(array_one_hundred_fifty.count) := j.close_price;

          if mod(array_one_hundred_fifty.count, 250) = 0 then

            v_one_hundred_fifty_sum := 0;
            for x in 1 .. array_one_hundred_fifty.count loop
              -- 求250天收盘价的和
              v_one_hundred_fifty_sum := v_one_hundred_fifty_sum +
                                         array_one_hundred_fifty(x);
            end loop;

            -- 删除数组中的第一个元素，将其与249个元素向前挪一位，并删除下标为250的元素
            for y in 1 .. array_one_hundred_fifty.count - 1 loop
              array_one_hundred_fifty(y) := array_one_hundred_fifty(y + 1);
            end loop;
            array_one_hundred_fifty.trim;

            -- 250天收盘价的平均值
            v_one_hundred_fifty_average := v_one_hundred_fifty_sum / 250;

            -- 向所有记录的FIVE列插入250天收盘价的平均值
            update stock_month t
               set t.ma250 = round(v_one_hundred_fifty_average, 2)
             where t.code_ = v_code
               and t.begin_date = j.begin_date
               and t.end_date = j.end_date;
          end if;
        end loop;
      end loop;
      commit;
    end;
  end WRITE_MA250;

  /*------------------------ 计算某一个月，所有股票的简单移动平均线 ---------------------*/
  procedure WRITE_MA_BY_DATE(p_current_month_begin_date in varchar2,
                             p_current_month_end_date   in varchar2) is
    -- 表示stock_code
    v_code varchar2(10);
    -- 表示5天收盘价的平均值
    v_five_average number := 0;
    -- 表示10天收盘价的平均值
    v_ten_average number := 0;
    -- 表示20天收盘价的平均值
    v_twenty_average number := 0;
    -- 表示60天收盘价的平均值
    v_sixty_average number := 0;
    -- 表示120天收盘价的平均值
    v_one_hundred_twenty_average number := 0;
    -- 表示250天收盘价的平均值
    v_two_hundred_fifty_average number := 0;
    -- 表示所有天收盘价的平均值
    --infiniteAverage number:=0;
    -- 用于判断某只股票的记录数是否可以计算均线
    v_average_num number;
    -- 开始日期
    v_begin_date date;
    -- 结束日期
    v_end_date date;
    -- 计算某只股票，在某个交易日之前的所有交易记录
    cursor cur_stock_row_num_by_date is
      select t.code_, count(*) row_num
        from stock_month t
       where t.end_date < to_date(p_current_month_begin_date, 'yyyy-mm-dd')
       group by t.code_;
  begin
    for i in cur_stock_row_num_by_date loop
      v_code        := i.code_;
      v_average_num := i.row_num;

      -- 查找某只股票的开始日期和结束日期
      begin
        select t.begin_date, t.end_date
          into v_begin_date, v_end_date
          from stock_month t
         where t.code_ = v_code
           and t.begin_date between
               to_date(p_current_month_begin_date, 'yyyy-mm-dd') and
               to_date(p_current_month_end_date, 'yyyy-mm-dd')
           and t.end_date between
               to_date(p_current_month_begin_date, 'yyyy-mm-dd') and
               to_date(p_current_month_end_date, 'yyyy-mm-dd');
      exception
        when no_data_found then
          DBMS_OUTPUT.put_line('代码为【' || v_code || '】的股票，
            在日期【' ||
                               p_current_month_begin_date || '】和【' ||
                               p_current_month_end_date || '】之间没有数据');
          continue;
      end;

      /*select
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=5 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=10 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=20 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=60 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=120 order by t.stock_date desc)),
             (select avg(stock_close) from(select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') and rownum<=250 order by t.stock_date desc))
             into fiveAverage,tenAverage,twentyAverage,sixtyAverage,oneHundredTwentyAverage,twoHundredFiftyAverage
      from dual;
      update stock_moving_average t
      set t.five=round(fiveAverage,2),t.ten=round(tenAverage,2),t.twenty=round(twentyAverage,2),
          t.sixty=round(sixtyAverage,2),t.one_hundred_twenty=round(oneHundredTwentyAverage,2),
          t.two_hundred_fifty=round(twoHundredFiftyAverage,2)
      where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
      commit;*/

      -- 每只股票都重置如下变量
      v_five_average               := null;
      v_ten_average                := null;
      v_twenty_average             := null;
      v_sixty_average              := null;
      v_one_hundred_twenty_average := null;
      v_two_hundred_fifty_average  := null;

      -- 更新所有股票某一天的5日均线
      if v_average_num >= 5 then
        select avg(d.close_price)
          into v_five_average
          from (select *
                  from stock_month t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_month_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 5;
      end if;

      -- 更新所有股票某一天的10日均线
      if v_average_num >= 10 then
        select avg(d.close_price)
          into v_ten_average
          from (select *
                  from stock_month t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_month_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 10;
      end if;

      -- 更新所有股票某一天的20日均线
      if v_average_num >= 20 then
        select avg(d.close_price)
          into v_twenty_average
          from (select *
                  from stock_month t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_month_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 20;
      end if;

      -- 更新所有股票某一天的60日均线
      if v_average_num >= 60 then
        select avg(d.close_price)
          into v_sixty_average
          from (select *
                  from stock_month t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_month_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 60;
      end if;

      -- 更新所有股票某一天的120日均线
      if v_average_num >= 120 then
        select avg(d.close_price)
          into v_one_hundred_twenty_average
          from (select *
                  from stock_month t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_month_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 120;
      end if;

      -- 更新所有股票某一天的250日均线
      if v_average_num >= 250 then
        select avg(d.close_price)
          into v_two_hundred_fifty_average
          from (select *
                  from stock_month t
                 where t.code_ = v_code
                   and t.end_date <=
                       to_date(p_current_month_end_date, 'yyyy-mm-dd')
                 order by t.begin_date desc) d
         where rownum <= 250;
      end if;

      -- 更新表stock_month的所有移动平均线
      update stock_month t
         set t.ma5   = nvl2(v_five_average, round(v_five_average, 2), null),
             t.ma10  = nvl2(v_ten_average, round(v_ten_average, 2), null),
             t.ma20  = nvl2(v_twenty_average,
                            round(v_twenty_average, 2),
                            null),
             t.ma60  = nvl2(v_sixty_average, round(v_sixty_average, 2), null),
             t.ma120 = nvl2(v_one_hundred_twenty_average,
                            round(v_one_hundred_twenty_average, 2),
                            null),
             t.ma250 = nvl2(v_two_hundred_fifty_average,
                            round(v_two_hundred_fifty_average, 2),
                            null)
       where t.code_ = v_code
         and t.begin_date = v_begin_date
         and t.end_date = v_end_date;

    -- 更新所有股票某一天的所有日均线
    /*select avg(d.stock_close) into infiniteAverage from(
                               select * from stock_moving_average t where t.stock_code=stockCode and t.stock_date<=to_date(stockDate,'yyyy-mm-dd') order by t.stock_date desc
                        ) d;
                        update stock_moving_average t set t.infinite=round(infiniteAverage,2) where t.stock_code=stockCode and t.stock_date=to_date(stockDate,'yyyy-mm-dd');
                        */
    end loop;
    commit;
  end WRITE_MA_BY_DATE;

  ---------------------------------------------------- 计算KD ------------------------------------------
  procedure WRITE_MONTH_KD_INIT as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 用于计算是否是第9个交易月
    num number;
    -- 返回全部code_
    cursor cur_all_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 查询某只股票最初的8个交易月的记录，并按生序排列
    cursor cur_single_stock_k is
      select *
        from (select *
                from stock_month t
               where t.code_ = v_code
               order by t.end_date asc)
       where rownum <= 8;
  begin
    -- 初始化每只股票第一个交易月的K和D字段
    for i in cur_all_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock_k loop
        num := num + 1;
        if num = 8 then
          -- 若无前一月K 值与D值，则可分别用50来代替
          update stock_month t
             set t.k = 50, t.d = 50
           where t.code_ = v_code
             and t.end_date = j.end_date;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MONTH_KD_INIT;

  procedure WRITE_MONTH_KD_RSV as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 9月内最高价
    v_nine_month_highest_price number;
    -- 9月内最低价
    v_nine_month_lowest_price number;
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- define cursor section.返回全部code_
    cursor cur_all_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 获取某只股票所有的月线级别的交易记录，并按升序排列
    cursor cur_single_stock is
      select *
        from stock_month t
       where t.code_ = v_code
       order by t.end_date asc;
  begin
    for i in cur_all_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock loop
        num := num + 1;
        if num >= 9 then
          -- 计算9月内最高价和最低价
          select max(highest_price), min(lowest_price)
            into v_nine_month_highest_price, v_nine_month_lowest_price
            from (select *
                    from stock_month t
                   where t.code_ = v_code
                     and t.end_date <= j.end_date
                   order by t.end_date desc)
           where rownum <= 9;
          -- 计算rsv
          update stock_month t
             set t.rsv =
                 (t.close_price - v_nine_month_lowest_price) /
                 (v_nine_month_highest_price - v_nine_month_lowest_price) * 100
           where t.code_ = v_code
             and t.end_date = j.end_date;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MONTH_KD_RSV;

  procedure WRITE_MONTH_KD_K as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- 表示KD指标的K
    v_temp_k number;
    -- 返回全部code_
    cursor cur_all_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 获取某只股票所有的月线级别的交易记录，并按升序排列
    cursor cur_single_stock is
      select *
        from stock_month t
       where t.code_ = v_code
       order by t.end_date asc;
  begin
    for i in cur_all_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock loop
        num := num + 1;
        if num = 8 then
          v_temp_k := j.k;
        end if;
        if num >= 9 then
          -- 计算K
          update stock_month t
             set t.k = 2 / 3 * v_temp_k + 1 / 3 * t.rsv
           where t.code_ = v_code
             and t.end_date = j.end_date;
          commit;
          select t.k
            into v_temp_k
            from stock_month t
           where t.code_ = v_code
             and t.end_date = j.end_date;
        end if;

      end loop;
    end loop;
  end WRITE_MONTH_KD_K;

  procedure WRITE_MONTH_KD_D as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 计算指标时使用，用于表示日期的累积
    num number;
    -- 表示KD指标的D
    v_temp_d number;
    -- 返回全部stock_code
    cursor cur_all_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 获取某只股票所有的月线级别的交易记录，并按升序排列
    cursor cur_single_stock is
      select *
        from stock_month t
       where t.code_ = v_code
       order by t.end_date asc;
  begin
    for i in cur_all_code loop
      v_code := i.code_;
      num    := 0;
      for j in cur_single_stock loop
        num := num + 1;
        if num = 8 then
          v_temp_d := j.d;
        end if;
        if num >= 9 then
          -- 计算D
          update stock_month t
             set t.d = 2 / 3 * v_temp_d + 1 / 3 * j.k
           where t.code_ = v_code
             and t.end_date = j.end_date;
          commit;
          select t.d
            into v_temp_d
            from stock_month t
           where t.code_ = v_code
             and t.end_date = j.end_date;
        end if;
      end loop;
    end loop;
  end WRITE_MONTH_KD_D;

  -------------------------------------------------- 计算某一日的KD ----------------------------------------
  procedure WRITE_MONTH_KD_BY_DATE_RSV(p_begin_date varchar2,
                                       p_end_date   varchar2) as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 9月内最高价
    v_nine_month_highest_price number;
    -- 9月内最低价
    v_nine_month_lowest_price number;
    -- 开始时间
    v_begin_date date;
    -- 结束时间
    v_end_date date;
    -- 返回全部stock_code
    cursor cur_all_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
  begin
    for i in cur_all_code loop
      v_code := i.code_;
      -- 计算9月内最高价和最低价
      select max(highest_price), min(lowest_price)
        into v_nine_month_highest_price, v_nine_month_lowest_price
        from (select *
                from stock_month t
               where t.code_ = v_code
                 and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
                 and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
               order by t.end_date desc)
       where rownum <= 9;
      -- 查找开始时间和结束时间
      select min(t.date_), max(t.date_)
        into v_begin_date, v_end_date
        from stock_transaction_data t
       where t.code_ = v_code
         and t.date_ between to_date(p_begin_date, 'yyyy-mm-dd') and
             to_date(p_end_date, 'yyyy-mm-dd');
      -- 计算某只股票某一月的RSV
      update stock_month t
         set t.rsv =
             (t.close_price - v_nine_month_lowest_price) /
             (v_nine_month_highest_price - v_nine_month_lowest_price) * 100
       where t.code_ = v_code
         and t.begin_date <= v_begin_date
         and t.end_date <= v_end_date;
      commit;
    end loop;
  end WRITE_MONTH_KD_BY_DATE_RSV;

  procedure WRITE_MONTH_KD_BY_DATE_K(p_begin_date varchar2,
                                     p_end_date   varchar2) as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 表示前一月K值
    v_temp_k number;
    -- 用于计数
    v_num number;
    -- 返回全部code_
    cursor cur_all_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 按照日期参数，获取某只股票最近两天的月线级别的交易记录，并按降序排列
    cursor cur_single_stock is
      select *
        from (select *
                from stock_month t
               where t.code_ = v_code
                 and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
                 and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
               order by t.begin_date desc)
       where rownum <= 2
       order by begin_date asc;
  begin
    for i in cur_all_code loop
      v_code := i.code_;
      v_num  := 0;
      for j in cur_single_stock loop
        v_num := v_num + 1;
        if v_num = 1 then
          v_temp_k := j.k;
        end if;
        if v_num = 2 then
          update stock_month t
             set t.k = 2 / 3 * v_temp_k + 1 / 3 * t.rsv
           where t.code_ = v_code
             and t.begin_date = j.begin_date
             and t.end_date = j.end_date;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MONTH_KD_BY_DATE_K;

  procedure WRITE_MONTH_KD_BY_DATE_D(p_begin_date varchar2,
                                     p_end_date   varchar2) as
    -- 表示某只股票的CODE_字段
    v_code varchar2(10);
    -- 表示前一月K值
    v_temp_d number;
    -- 用于计数
    v_num number;
    -- 返回全部code_
    cursor cur_all_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 按照日期参数，获取某只股票最近两天的月线级别的交易记录，并按降序排列
    cursor cur_single_stock is
      select *
        from (select *
                from stock_month t
               where t.code_ = v_code
                 and t.begin_date <= to_date(p_begin_date, 'yyyy-mm-dd')
                 and t.end_date <= to_date(p_end_date, 'yyyy-mm-dd')
               order by t.begin_date desc)
       where rownum <= 2
       order by begin_date asc;
  begin
    for i in cur_all_code loop
      v_code := i.code_;
      v_num  := 0;
      for j in cur_single_stock loop
        v_num := v_num + 1;
        if v_num = 1 then
          v_temp_d := j.d;
        end if;
        if v_num = 2 then
          update stock_month t
             set t.d = 2 / 3 * v_temp_d + 1 / 3 * t.k
           where t.code_ = v_code
             and t.begin_date = j.begin_date
             and t.end_date = j.end_date;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MONTH_KD_BY_DATE_D;

  /*-------------------------------------------- 计算MACD ----------------------------------------------*/
  procedure WRITE_MONTH_MACD_INIT as
    -- 表示code_
    v_stock_month_code varchar2(10);
    -- 返回全部code_
    cursor cur_all_stock_month_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 获取每只股票第一个交易月的日期
    cursor cur_first_stock_month_date is
      select *
        from (select t.begin_date
                from stock_month t
               where t.code_ = v_stock_month_code
               order by t.begin_date asc)
       where rownum <= 1;
  begin
    -- 初始化每只股票第一个交易月的ema12,ema26,dif和dea字段
    for i in cur_all_stock_month_code loop
      v_stock_month_code := i.code_;
      for j in cur_first_stock_month_date loop
        update stock_month t
           set t.ema12 = t.close_price,
               t.ema26 = t.close_price,
               t.dif   = 0,
               t.dea   = 0
         where t.code_ = v_stock_month_code
           and t.begin_date = j.begin_date;
        commit;
      end loop;
    end loop;
  end WRITE_MONTH_MACD_INIT;

  procedure WRITE_MONTH_MACD_EMA as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示code_
    v_stock_month_code varchar2(10);
    -- 返回全部code_
    cursor cur_all_stock_month_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 获取每只股票第一个交易月的日期
    cursor cur_first_stock_month_date is
      select *
        from (select t.begin_date
                from stock_month t
               where t.code_ = v_stock_month_code
               order by t.begin_date asc)
       where rownum <= 1;
    -- 根据v_stock_month_code选出某只股票的除第一月外的全部交易记录，按升序排列
    cursor cur_all_stock_month is
      select distinct *
        from stock_month t
       where t.code_ = v_stock_month_code
         and t.end_date > v_first_date
       order by t.begin_date asc;
  begin
    -- 计算每只股票其余交易月的ema12,ema26,dif和dea字段
    for i in cur_all_stock_month_code loop
      v_stock_month_code := i.code_;
      -- 用记录是第一个交易月的字段初始化相关变量
      for x in cur_first_stock_month_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from stock_month t
         where t.code_ = v_stock_month_code
           and t.begin_date = x.begin_date;
      end loop;

      select *
        into v_first_date
        from (select t.begin_date
                from stock_month t
               where t.code_ = v_stock_month_code
               order by t.begin_date asc)
       where rownum <= 1;
      for j in cur_all_stock_month loop
        -- 对于其余交易月，更新ema12,ema26,dif和dea字段
        update stock_month t
           set t.ema12 = v_pre_ema12 * 11 / 13 + j.close_price * 2 / 13,
               t.ema26 = v_pre_ema26 * 25 / 27 + j.close_price * 2 / 27
         where t.code_ = v_stock_month_code
           and t.begin_date = j.begin_date;
        commit;
        -- 用于计算下一个交易月时使用
        select t.ema12
          into v_pre_ema12
          from stock_month t
         where t.code_ = v_stock_month_code
           and t.begin_date = j.begin_date;
        select t.ema26
          into v_pre_ema26
          from stock_month t
         where t.code_ = v_stock_month_code
           and t.begin_date = j.begin_date;
      end loop;
    end loop;
  end WRITE_MONTH_MACD_EMA;

  procedure WRITE_MONTH_MACD_DIF as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示code_
    v_stock_month_code varchar2(10);
    -- 返回全部code_
    cursor cur_all_stock_month_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 获取每只股票第一个交易月的日期
    cursor cur_first_stock_month_date is
      select *
        from (select t.begin_date
                from stock_month t
               where t.code_ = v_stock_month_code
               order by t.begin_date asc)
       where rownum <= 1;
    -- 根据stockCode选出某只股票的除第一月以外的全部交易记录，按升序排列
    cursor cur_all_stock_month is
      select distinct *
        from stock_month t
       where t.code_ = v_stock_month_code
         and t.begin_date > v_first_date
       order by t.begin_date asc;
  begin
    -- 计算每只股票其余交易月的ema12,ema26,dif和dea字段
    for i in cur_all_stock_month_code loop
      v_stock_month_code := i.code_;
      -- 用记录是第一个交易月的字段初始化相关变量
      for x in cur_first_stock_month_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from stock_month t
         where t.code_ = v_stock_month_code
           and t.begin_date = x.begin_date;
      end loop;
      select *
        into v_first_date
        from (select t.begin_date
                from stock_month t
               where t.code_ = v_stock_month_code
               order by t.begin_date asc)
       where rownum <= 1;
      for j in cur_all_stock_month loop
        update stock_month t
           set t.dif = t.ema12 - t.ema26
         where t.code_ = v_stock_month_code
           and t.begin_date = j.begin_date;
        commit;
        select t.dif
          into v_pre_dif
          from stock_month t
         where t.code_ = v_stock_month_code
           and t.begin_date = j.begin_date;
      end loop;
    end loop;
  end WRITE_MONTH_MACD_DIF;

  procedure WRITE_MONTH_MACD_DEA as
    v_pre_ema12  number;
    v_pre_ema26  number;
    v_pre_dif    number;
    v_pre_dea    number;
    v_first_date date;
    -- 表示code_
    v_stock_month_code varchar2(10);
    -- 返回全部code_
    cursor cur_all_stock_month_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 获取每只股票第一个交易月的日期
    cursor cur_first_stock_month_date is
      select *
        from (select t.begin_date
                from stock_month t
               where t.code_ = v_stock_month_code
               order by t.begin_date asc)
       where rownum <= 1;
    -- 根据stockCode选出某只股票的除第一月以外的全部交易记录，按升序排列
    cursor cur_all_stock_month is
      select distinct *
        from stock_month t
       where t.code_ = v_stock_month_code
         and t.begin_date > v_first_date
       order by t.begin_date asc;
  begin
    -- 计算每只股票其余交易月的ema12,ema26,dif和dea字段
    for i in cur_all_stock_month_code loop
      v_stock_month_code := i.code_;
      -- 用记录是第一个交易月的字段初始化相关变量
      for x in cur_first_stock_month_date loop
        select t.ema12, t.ema26, t.dif, t.dea
          into v_pre_ema12, v_pre_ema26, v_pre_dif, v_pre_dea
          from stock_month t
         where t.code_ = v_stock_month_code
           and t.begin_date = x.begin_date;
      end loop;
      select *
        into v_first_date
        from (select t.begin_date
                from stock_month t
               where t.code_ = v_stock_month_code
               order by t.begin_date asc)
       where rownum <= 1;
      for j in cur_all_stock_month loop
        update stock_month t
           set t.dea = v_pre_dea * 8 / 10 + t.dif * 2 / 10
         where t.code_ = v_stock_month_code
           and t.begin_date = j.begin_date;
        commit;
        select t.dea
          into v_pre_dea
          from stock_month t
         where t.code_ = v_stock_month_code
           and t.begin_date = j.begin_date;
      end loop;
    end loop;
  end WRITE_MONTH_MACD_DEA;

  /*----------------------------------- 计算某一月MACD -------------------------------------------------*/
  procedure WRITE_MONTH_MACD_EMA_BY_DATE(p_stock_month_begin_date in varchar2,
                                         p_stock_month_end_date   in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示code_
    v_stock_month_code varchar2(10);
    -- 返回全部code_
    cursor cur_all_stock_month_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 获取某只股票最近两个月的记录
    cursor cur_single_stock_month is
      select *
        from (select *
                from (select *
                        from stock_month t
                       where t.code_ = v_stock_month_code
                         and t.begin_date <=
                             to_date(p_stock_month_begin_date, 'yyyy-mm-dd')
                         and t.end_date <=
                             to_date(p_stock_month_end_date, 'yyyy-mm-dd')
                       order by t.begin_date desc)
               where rownum <= 2) o
       order by o.begin_date asc;
  begin
    for i in cur_all_stock_month_code loop
      v_stock_month_code := i.code_;
      for j in cur_single_stock_month loop
        if j.begin_date != to_date(p_stock_month_begin_date, 'yyyy-mm-dd') and
           j.end_date != to_date(p_stock_month_end_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
        else
          update stock_month t
             set t.ema12 = v_pre_ema12 * 11 / 13 + j.close_price * 2 / 13,
                 t.ema26 = v_pre_ema26 * 25 / 27 + j.close_price * 2 / 27
           where t.code_ = v_stock_month_code
             and t.begin_date = j.begin_date
             and t.end_date = j.end_date;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MONTH_MACD_EMA_BY_DATE;

  procedure WRITE_MONTH_MACD_DIF_BY_DATE(p_stock_month_begin_date in varchar2,
                                         p_stock_month_end_date   in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示code_
    v_stock_month_code varchar2(10);
    -- 返回全部code_
    cursor cur_all_stock_month_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 获取某只股票最近两个月的记录
    cursor cur_single_stock_month is
      select *
        from (select *
                from (select *
                        from stock_month t
                       where t.code_ = v_stock_month_code
                         and t.begin_date <=
                             to_date(p_stock_month_begin_date, 'yyyy-mm-dd')
                         and t.end_date <=
                             to_date(p_stock_month_end_date, 'yyyy-mm-dd')
                       order by t.begin_date desc)
               where rownum <= 2) o
       order by o.begin_date asc;
  begin
    for i in cur_all_stock_month_code loop
      v_stock_month_code := i.code_;
      for j in cur_single_stock_month loop
        if j.begin_date != to_date(p_stock_month_begin_date, 'yyyy-mm-dd') and
           j.end_date != to_date(p_stock_month_end_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
        else
          update stock_month t
             set t.dif = t.ema12 - t.ema26
           where t.code_ = v_stock_month_code
             and t.begin_date = j.begin_date
             and t.end_date = j.end_date;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MONTH_MACD_DIF_BY_DATE;

  procedure WRITE_MONTH_MACD_DEA_BY_DATE(p_stock_month_begin_date in varchar2,
                                         p_stock_month_end_date   in varchar2) as
    v_pre_ema12 number;
    v_pre_ema26 number;
    v_pre_dea   number;
    -- 表示code_
    v_stock_month_code varchar2(10);
    -- 返回全部code_
    cursor cur_all_stock_month_code is
      select distinct t.code_ from stock_month t order by t.code_ asc;
    -- 获取某只股票最近两个月的记录
    cursor cur_single_stock_month is
      select *
        from (select *
                from (select *
                        from stock_month t
                       where t.code_ = v_stock_month_code
                         and t.begin_date <=
                             to_date(p_stock_month_begin_date, 'yyyy-mm-dd')
                         and t.end_date <=
                             to_date(p_stock_month_end_date, 'yyyy-mm-dd')
                       order by t.begin_date desc)
               where rownum <= 2) o
       order by o.begin_date asc;
  begin
    for i in cur_all_stock_month_code loop
      v_stock_month_code := i.code_;
      for j in cur_single_stock_month loop
        if j.begin_date != to_date(p_stock_month_begin_date, 'yyyy-mm-dd') and
           j.end_date != to_date(p_stock_month_end_date, 'yyyy-mm-dd') then
          v_pre_ema12 := j.ema12;
          v_pre_ema26 := j.ema26;
          v_pre_dea   := j.dea;
        else
          update stock_month t
             set t.dea = v_pre_dea * 8 / 10 + t.dif * 2 / 10
           where t.code_ = v_stock_month_code
             and t.begin_date = j.begin_date
             and t.end_date = j.end_date;
          commit;
        end if;
      end loop;
    end loop;
  end WRITE_MONTH_MACD_DEA_BY_DATE;

end PKG_MONTH;